﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model;

namespace DataAccess
{
    public static class PersonDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=Brigada; Integrated Security=true";

        public static Person InsertNewPerson(Person newPerson)
        {
            if (newPerson != null)
            {
                SqlConnection connection = new SqlConnection(ConnString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    String text = "INSERT INTO [dbo].[Person](Id,CiPassport,Name, PaternalName, MaternalName, BirthDate,Address,Zone,Phone,Ocupation,CivilStatus,BornCity,RegistrationDate,Status)";
                    text = text + "VALUES(@Id,@CiPassport,@Name, @PaternalName, @MaternalName,@BirthDate,@Address,@Zone,@Phone,@Ocupation,@CivilStatus,@BornCity,@RegistrationDate,@Status)";
                    command.CommandText = text;

                    command.Parameters.AddWithValue("@Id", newPerson.Id);
                    command.Parameters.AddWithValue("@CiPassport", newPerson.CiPassport);
                    command.Parameters.AddWithValue("@Name", newPerson.Name);
                    command.Parameters.AddWithValue("@PaternalName", newPerson.PaternalName);
                    command.Parameters.AddWithValue("@MaternalName", newPerson.MaternalName);
                    command.Parameters.AddWithValue("@BirthDate", newPerson.BirthDate);
                    //command.Parameters.AddWithValue("@Gender", newPerson.Gender);
                    
                    command.Parameters.AddWithValue("@Address", newPerson.Address);
                    command.Parameters.AddWithValue("@Zone", newPerson.Zone);
                    //command.Parameters.AddWithValue("@Nationality", newPerson.Nationality);
                    command.Parameters.AddWithValue("@Phone", newPerson.Phone);
                    command.Parameters.AddWithValue("@Ocupation", newPerson.Ocupation);
                    command.Parameters.AddWithValue("@CivilStatus", newPerson.CivilStatus);
                    command.Parameters.AddWithValue("@BornCity", newPerson.BornCity);
                    //command.Parameters.AddWithValue("@BornArea", newPerson.BornArea);
                    //command.Parameters.AddWithValue("@LevelOfEducation", newPerson.LevelOfEducation);
                    //command.Parameters.AddWithValue("@ActivityStatus", newPerson.ActivityStatus);
                    //command.Parameters.AddWithValue("@WorkPlace", newPerson.WorkPlace);
                    command.Parameters.AddWithValue("@RegistrationDate", newPerson.RegistrationDate);
                    command.Parameters.AddWithValue("@Status", newPerson.Status);
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        return null;
                    }
                    return newPerson;
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static Person UpdatePerson(Person newPerson)
        {

            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                String text = "UPDATE [dbo].[Person]";
                text = text + "SET CiPassport=@CiPassport,Name=@Name, PaternalName=@PaternalName, MaternalName=@MaternalName, BirthDate@BirthDate,Gender=@Gender,Address=@Address,Zone=@Zone,Phone=@Phone,Ocupation=@Ocupation,CivilStatus=@CivilStatus,BornCity=@BornCity,BornArea=@BornArea,LvelOfEducation=@LevelOfEducation,ActivityStatus=@ActivityStatus,WorkPlace=@WorkPlace,Status=@Status where Id=@Id";
                command.CommandText = text;
                command.Parameters.AddWithValue("Id", newPerson.Id);
                command.Parameters.AddWithValue("CiPassport", newPerson.CiPassport);
                command.Parameters.AddWithValue("Name", newPerson.Name);
                command.Parameters.AddWithValue("PaternalName", newPerson.PaternalName);
                command.Parameters.AddWithValue("MaternalName", newPerson.MaternalName);
                command.Parameters.AddWithValue("BirthDate", newPerson.BirthDate);
                command.Parameters.AddWithValue("Gender", newPerson.Gender);
                command.Parameters.AddWithValue("Phone", newPerson.Phone);
                command.Parameters.AddWithValue("Address", newPerson.Address);
                command.Parameters.AddWithValue("Zone", newPerson.Zone);
                command.Parameters.AddWithValue("Nationality", newPerson.Nationality);
                command.Parameters.AddWithValue("Ocupation", newPerson.Ocupation);
                command.Parameters.AddWithValue("CivilStatus", newPerson.CivilStatus);
                command.Parameters.AddWithValue("BornCity", newPerson.BornCity);
                command.Parameters.AddWithValue("BornArea", newPerson.BornArea);
                command.Parameters.AddWithValue("LevelOfEducation", newPerson.LevelOfEducation);
                command.Parameters.AddWithValue("ActivityStatus", newPerson.ActivityStatus);
                command.Parameters.AddWithValue("WorkPlace", newPerson.WorkPlace);
                command.Parameters.AddWithValue("RegistrationDate", newPerson.RegistrationDate);
                command.Parameters.AddWithValue("Status", newPerson.Status);
                connection.Open();
                command.ExecuteNonQuery();
                return newPerson;

            }
            catch (Exception ex)
            {

                return null;

            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }

        }

        public static Person GetPersonById(long ci)
        {
            Person person = new Person();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Person] WHERE CiPassport=@CiPassport";
                cmd.Parameters.AddWithValue("CiPassport", ci);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    person.Id = reader["Id"].ToString();
                    person.CiPassport = Convert.ToInt64(reader["CiPassport"].ToString());
                    person.Name = reader["Name"].ToString();
                    person.PaternalName = reader["PaternalName"].ToString();
                    person.MaternalName = reader["MaternalName"].ToString();
                    person.BirthDate = Convert.ToDateTime(reader["BornDate"].ToString());
                    person.Gender = Convert.ToChar(reader["Gender"].ToString());
                    person.Address = reader["Address"].ToString();
                    person.Zone = reader["Zone"].ToString();
                    person.Nationality = reader["Nationality"].ToString();
                    person.Ocupation = reader["Ocupation"].ToString();
                    person.CivilStatus = reader["CivilStatus"].ToString();
                    person.BornCity = reader["BornCity"].ToString();
                    person.BornArea = reader["BornArea"].ToString();
                    person.LevelOfEducation = reader["LevelOfEducation"].ToString();
                    person.ActivityStatus = reader["ActivityStatus"].ToString();
                    person.WorkPlace = reader["WorkPlace"].ToString();
                    person.Status = Convert.ToBoolean(reader["Status"].ToString());
                }
                return person;
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

    }
}
